﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using ResourceBlender.Core;

namespace ResourceBlender.DAL.Import
{
    public abstract class ResourceImporterBase
    {
        /// <summary>
        /// Inserts bundles, elements and translations into the database.
        /// </summary>
        /// <param name="bundles">The bundles to import. Should be in the format &lt;bundle, &lt;elementname, elementData&gt;&gt;</param>
        /// <param name="application">The application into which to import the bundles.</param>
        /// <param name="log">A delegate to which log message should be sent.</param>
        protected static void ProcessBundles(Dictionary<string, Dictionary<string, RBElement>> bundles, RBApplication application, StatusMessage.StatusChangedEventHandler log)
        {
            // <bundle, <elementname, elementData>>
            log(new StatusMessage(I18nUtils.GetString("UIStrings", "ParseDoneStartingImport"), StatusMessage.StatusMessageType.Success));
            Database db = null;
            try
            {
                db = new Database();
                db.OpenConnection();
                int numBundles = 0,
                    numElements = 0,
                    numResources = 0;
                using (DbTransaction trans = db.BeginTransaction())
                {
                    using (Command cmd = db.CreateCommand(trans))
                    using (Command cmdInsertBundle = db.CreateCommand(trans))
                    using (Command cmdCheckExisting = db.CreateCommand(trans))
                    using (Command cmdInsertElement = db.CreateCommand(trans))
                    using (Command cmdFindSimilar = db.CreateCommand(trans))
                    using (Command cmdInsertResource = db.CreateCommand(trans))
                    using (Command cmdInsertResourceComment = db.CreateCommand(trans))
                    using (Command cmdMaxRow = db.CreateCommand(trans))
                    {
                        Hashtable appBundles = new Hashtable(); // <bundlename, bundleid>
                        cmd.CommandText = "SELECT id, name FROM " + db.TablePrefix + "bundles WHERE application = @id";
                        cmd.AddParameterWithValue("@id", application.ID);
                        DbDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            appBundles.Add(reader["name"], reader["id"]);
                        }
                        reader.Close();

                        cmdInsertBundle.CommandText = "INSERT INTO " + db.TablePrefix + "bundles (id, name, application) VALUES (@id, @name, @application)";
                        cmdInsertBundle.AddParameterWithValue("@id", "");
                        cmdInsertBundle.AddParameterWithValue("@name", "");
                        cmdInsertBundle.AddParameterWithValue("@application", application.ID);

                        cmdCheckExisting.CommandText = "SELECT resourceid FROM " + db.TablePrefix + "elements WHERE applicationid = @appId AND bundle = @bundle AND elementname = @element";
                        cmdCheckExisting.AddParameterWithValue("@appId", application.ID);
                        cmdCheckExisting.AddParameterWithValue("@bundle", "");
                        cmdCheckExisting.AddParameterWithValue("@element", "");

                        cmdInsertElement.CommandText = "INSERT INTO " + db.TablePrefix + "elements (applicationid, bundle, elementname, resourceid, comment) VALUES (@appId, @bundle, @element, @tId, @comment)";
                        cmdInsertElement.AddParameterWithValue("@appId", application.ID);
                        cmdInsertElement.AddParameterWithValue("@bundle", "");
                        cmdInsertElement.AddParameterWithValue("@element", "");
                        cmdInsertElement.AddParameterWithValue("@tId", "");
                        cmdInsertElement.AddParameterWithValue("@comment", "");

                        cmdFindSimilar.CommandText = "SELECT resourceid FROM " + db.TablePrefix + "resources WHERE language = @language AND translation = @translation";
                        cmdFindSimilar.AddParameterWithValue("@language", application.DefaultLanguage); // Only match if the default language for this app. is present in the uploaded resources
                        cmdFindSimilar.AddParameterWithValue("@translation", "");

                        switch (db.InstanceType)
                        {
                            case DatabaseType.MySQL:
                                cmdInsertResource.CommandText = string.Format("INSERT INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES (@tId, @language, @translation, @translatorcomment) ON DUPLICATE KEY UPDATE translation = @translation, translatorcomment = @translatorcomment", db.TablePrefix);
                                cmdInsertResourceComment.CommandText = string.Format("INSERT INTO {0}resourcecomments (resourceid, comment) VALUES (@tID, @comment) ON DUPLICATE KEY UPDATE comment = @comment", db.TablePrefix);
                                break;
                            case DatabaseType.SQLServer:
                                cmdInsertResource.CommandText = string.Format("UPDATE {0}resources SET translation = @translation, translatorcomment = @translatorcomment WHERE resourceid = @tId AND language = @language; IF @@ROWCOUNT = 0 INSERT INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES (@tId, @language, @translation, @translatorcomment)", db.TablePrefix);
                                cmdInsertResourceComment.CommandText = string.Format("UPDATE {0}resourcecomments SET comment = @comment WHERE resourceid = @tID; IF @@ROWCOUNT = 0 INSERT INTO {0}resourcecomments (resourceid, comment) VALUES (@tID, @comment)", db.TablePrefix);
                                break;
                            case DatabaseType.SQLite:
                                cmdInsertResource.CommandText = string.Format("INSERT OR REPLACE INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES (@tId, @language, @translation, @translatorcomment)", db.TablePrefix);
                                cmdInsertResourceComment.CommandText = string.Format("INSERT OR REPLACE INTO {0}resourcecomments (resourceid, comment) VALUES (@tID, @comment)", db.TablePrefix);
                                break;
                        }
                        cmdInsertResource.AddParameterWithValue("@tID", "");
                        cmdInsertResource.AddParameterWithValue("@language", "");
                        cmdInsertResource.AddParameterWithValue("@translation", "");
                        cmdInsertResource.AddParameterWithValue("@translatorcomment", "");

                        cmdInsertResourceComment.AddParameterWithValue("@tID", "");
                        cmdInsertResourceComment.AddParameterWithValue("@comment", "");

                        cmdMaxRow.CommandText = "SELECT MAX(resourceid) FROM " + db.TablePrefix + "resources";

                        int resourceId = 0;
                        string bundleId;
                        foreach (KeyValuePair<string, Dictionary<string, RBElement>> bundle in bundles)
                        { // <bundle, <elementName, elementData>>
                            log(new StatusMessage(string.Format(I18nUtils.GetString("UIStrings", "ImportingBundleName"), bundle.Key), StatusMessage.StatusMessageType.Status));
                            numBundles++;
                            if (appBundles.ContainsKey(bundle.Key))
                            { // bundle exists in application
                                bundleId = appBundles[bundle.Key].ToString();
                            }
                            else
                            { // create a bundle
                                bundleId = Utils.GenID(true);
                                cmdInsertBundle.Parameters["@id"].Value = bundleId;
                                cmdInsertBundle.Parameters["@name"].Value = bundle.Key;
                                cmdInsertBundle.ExecuteNonQuery();
                                appBundles.Add(bundle.Key, bundleId);
                            }
                            cmdCheckExisting.Parameters["@bundle"].Value = bundleId;
                            cmdInsertElement.Parameters["@bundle"].Value = bundleId;
                            int logIncrements = bundle.Value.Count >= 4 ? bundle.Value.Count / 4 : 1;
                            int elementsThisBundle = 0;
                            foreach (KeyValuePair<string, RBElement> element in bundle.Value)
                            { // <elementName, elementData>
                                elementsThisBundle++;
                                cmdCheckExisting.Parameters["@element"].Value = element.Key;
                                reader = cmdCheckExisting.ExecuteReader();
                                if (reader.HasRows)
                                { // the element already exists, overwrite existing languages with the ones from the bundle
                                    reader.Read();
                                    int.TryParse(reader[0].ToString(), out resourceId);
                                    reader.Close();
                                }
                                else
                                { // no matching elements, create one first
                                    reader.Close();
                                    cmdInsertElement.Parameters["@element"].Value = element.Key;

                                    // check for other resources which might be have the same translation, then append the translations in this bundle to them instead of creating a duplicate resource. 
                                    bool readId = false;
                                    if (element.Value.Resource.Translations.ContainsKey(application.DefaultLanguage))
                                    {
                                        cmdFindSimilar.Parameters["@translation"].Value = element.Value.Resource.Translations[application.DefaultLanguage].Translation;
                                        reader = cmdFindSimilar.ExecuteReader();
                                        if (reader.HasRows)
                                        { // resource of current element matches one already in the database, use the existing resource ID
                                            reader.Read();
                                            resourceId = reader.GetInt32(0);
                                            readId = true;
                                        }
                                        reader.Close();
                                    }
                                    if (!readId)
                                    { // no matching resources, find the next available resource ID
                                        using (DbDataReader maxIdReader = cmdMaxRow.ExecuteReader(CommandBehavior.SingleRow))
                                        {
                                            if (maxIdReader.HasRows)
                                            {
                                                maxIdReader.Read();
                                                int.TryParse(maxIdReader[0].ToString(), out resourceId);
                                                resourceId++;
                                            }
                                            maxIdReader.Close();
                                        }
                                    }
                                    cmdInsertElement.Parameters["@tId"].Value = resourceId;
                                    if (string.IsNullOrEmpty(element.Value.Comment))
                                    {
                                        cmdInsertElement.Parameters["@comment"].Value = DBNull.Value;
                                    }
                                    else
                                    {
                                        cmdInsertElement.Parameters["@comment"].Value = element.Value.Comment;
                                    }
                                    cmdInsertElement.ExecuteNonQuery();
                                }
                                cmdInsertResource.Parameters["@tId"].Value = resourceId;

                                RBResource resource = element.Value.Resource;
                                if (!string.IsNullOrEmpty(resource.Comment))
                                {
                                    cmdInsertResourceComment.Parameters["@tID"].Value = resourceId;
                                    cmdInsertResourceComment.Parameters["@comment"].Value = resource.Comment;
                                    cmdInsertResourceComment.ExecuteNonQuery();
                                }
                                foreach (KeyValuePair<string, RBTranslation> translation in resource.Translations)
                                { // <language, translation>>
                                    numResources++;
                                    cmdInsertResource.Parameters["@language"].Value = translation.Key;
                                    cmdInsertResource.Parameters["@translation"].Value = translation.Value.Translation;
                                    if (string.IsNullOrEmpty(translation.Value.Comment))
                                    {
                                        cmdInsertResource.Parameters["@translatorcomment"].Value = DBNull.Value;
                                    }
                                    else
                                    {
                                        cmdInsertResource.Parameters["@translatorcomment"].Value = translation.Value.Comment;
                                    }
                                    try
                                    {
                                        cmdInsertResource.ExecuteNonQuery();
                                    }
                                    catch (Exception ex)
                                    {
                                        log(new StatusMessage(ex.Message, StatusMessage.StatusMessageType.Error));
                                    }
                                }
                                if (logIncrements > 0 && elementsThisBundle % logIncrements == 0)
                                {
                                    log(new StatusMessage(string.Format(I18nUtils.GetString("UIStrings", "ImportedProgressElements"), elementsThisBundle, bundle.Value.Count), StatusMessage.StatusMessageType.Success));
                                }
                            }
                            numElements += elementsThisBundle;
                        }
                    }
                    trans.Commit();
                    log(new StatusMessage(string.Format(I18nUtils.GetString("UIStrings", "ImportSummaryDesc"), numResources, numBundles, numElements), StatusMessage.StatusMessageType.Success));
                }
            }
            catch (Exception ex)
            {
                log(new StatusMessage(ex.Message, StatusMessage.StatusMessageType.Error));
            }
            finally
            {
                if (db != null && db.Connected)
                {
                    try
                    {
                        db.CloseConnection();
                    }
                    catch { }
                }
            }
        }
    }
}
